﻿using KPIS.GERP.Models;
using KPIS.GERP.Models.Class;
using KPIS.GERP.Models.ICM;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Dynamic;
using System.Linq.Expressions;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using System.Data;
using System.Data.Entity;
using System.Web.Mvc;
using KPIS.GERP.Models.SYS;
using KPIS.GERP.Models.ICM.ViewModel;
using System.Data.SqlClient;


namespace KPIS.GERP.WCF.GIMS
{
    [ServiceContract]
    public interface IDeliveryNoteService
    {


        [OperationContract]
        IEnumerable<DeliveryNote> SelectDeliveryNote(DeliveryNote criticalmodel = null, List<string> includeSubObjectString = null);

        [OperationContract]
        IEnumerable<PurchaseOrder> SelectPurchaseOrder(PurchaseOrder criticalmodel = null, List<string> includeSubObjectString = null);
       
        [OperationContract]
        IEnumerable<PurchaseOrderInstallment> SelectPurchaseOrderInsatall(PurchaseOrderInstallment criticalmodel = null, List<string> includeSubObjectString = null);


        [OperationContract]
        int Insert(DeliveryNote model, int createByUserId);

        [OperationContract]
        void DeleteDeliveryNote(int id, int deleteByUserId);


        #region UpdateDeliverNote
        [OperationContract]
        void Update(DeliveryNote model, int createByUserId);
        #endregion
        //[OperationContract]
        //List<SelectListItem> GetPoName(PurchaseOrder criteriaModel = null);

        //[OperationContract]
        //void delete(DeliveryNote model, int deleteByUserId);

        [OperationContract]
        int InsertMoneyRequest(MoneyRequest model);

        [OperationContract]
        void UpdateUsed(DeliveryNote model, int createByUserId);
    }

    [ServiceErrorBehavior(typeof(ElmahErrorHandler))]
    public class DeliveryNoteService : IDeliveryNoteService
    {
        public IEnumerable<DeliveryNote> SelectDeliveryNote(DeliveryNote criteriaModel = null, List<string> includeSubObjectString = null)
        {
            try
            {
                using (var context = new GERPContext(false))
                {
                    IQueryable<DeliveryNote> query = context.DeliveryNotes;

                    if (includeSubObjectString != null)
                    {
                        query = includeSubObjectString.Aggregate(query, (current, includePath) => current.Include(includePath));
                    }

                    if (criteriaModel != null)
                    {
                        object[] param;
                        query = query.Where(KPIS.GERP.Models.Class.Utility.GenerateWhereString(criteriaModel, out param), param);
                    }

                    var data = query.ToList();


                    return data;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public IEnumerable<PurchaseOrderInstallment> SelectPurchaseOrderInsatall(PurchaseOrderInstallment criticalmodel = null, List<string> includeSubObjectString = null)
        {
            try
            {
                using (var context = new GERPContext(false))
                {
                    IQueryable<PurchaseOrderInstallment> query = context.PurchaseOrderInstallments;
                    if (includeSubObjectString != null)
                    {
                        query = includeSubObjectString.Aggregate(query, (current, includePath) => current.Include(includePath));
                    }
                    if (criticalmodel != null)
                    {
                        object[] param;
                        query = query.Where(KPIS.GERP.Models.Class.Utility.GenerateWhereString(criticalmodel, out param), param);

                    }

                    var data = query.ToList();

                    return data;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public IEnumerable<PurchaseOrder> SelectPurchaseOrder(PurchaseOrder criticalmodel = null, List<string> includeSubObjectString = null)
        {
            try
            {
                using (var context = new GERPContext(false))
                {
                    IQueryable<PurchaseOrder> query = context.PurchaseOrders;
                    
                    if (includeSubObjectString != null)
                    {
                        query = includeSubObjectString.Aggregate(query, (current, includePath) => current.Include(includePath));
                    }
                    if (criticalmodel != null)
                    {
                        object[] param;
                        query = query.Where(KPIS.GERP.Models.Class.Utility.GenerateWhereString(criticalmodel, out param), param);

                    }
              
                    var data = query.ToList();

                    return data;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int InsertMoneyRequest(MoneyRequest model) {
            int ret = 0;
            try
            {
                using (var context = new GERPContext(false))
                {
                    StringBuilder sb = new StringBuilder();
                    sb.Append("INSERT INTO fim_money_requests (");
                    sb.Append(" bgm_source_seq");
                    sb.Append(" ,event_seq");
                    sb.Append(" ,money_req_date");
                    sb.Append(" ,use_advance");
                    sb.Append(" ,ref_no");
                    sb.Append(" ,doc_ref_no");
                    sb.Append(" ,money_req_name");
                    sb.Append(" ,bgm_proj_oper_seq");
                    sb.Append(" ,emp_seq");
                    sb.Append(" ,job_site_seq");//
                    sb.Append(" ,contact_seq");
                    sb.Append(" ,icm_dn_seq");
                    sb.Append(" ,record_status");
                    sb.Append(" ,created_by");
                    sb.Append(" ,created_when");
                    sb.Append(" ,allow");
                    sb.Append(" ,bgm_year");
                    sb.Append(" ,work_area_seq");
                    sb.Append(")");

                    sb.Append(" VALUES(");
                    sb.Append(" @bgm_source_seq");
                    sb.Append(" ,@event_seq");
                    sb.Append(" ,@money_req_date");
                    sb.Append(" ,@use_advance");
                    sb.Append(" ,@ref_no");
                    sb.Append(" ,@doc_ref_no");
                    sb.Append(" ,@money_req_name");
                    sb.Append(" ,@bgm_proj_oper_seq");
                    sb.Append(" ,@emp_seq");
                    sb.Append(" ,@job_site_seq");
                    sb.Append(" ,@contact_seq");//
                    sb.Append(" ,@icm_dn_seq");
                    sb.Append(" ,@record_status");
                    sb.Append(" ,@created_by");
                    sb.Append(" ,@created_when");
                    sb.Append(" ,@allow");
                    sb.Append(" ,@bgm_year");
                    sb.Append(" ,@work_area_seq");
                    sb.Append(");");

                    int work_area_seq = GetWorkAreaId(model.JobSiteId);

                    ret = context.Database.ExecuteSqlCommand(sb.ToString()
                        , new SqlParameter("bgm_source_seq", model.BgSourceId)
                        , new SqlParameter("event_seq", model.EventId)
                        , new SqlParameter("money_req_date", model.MoneyReqDate)
                        , new SqlParameter("use_advance", model.UseAdvance)
                        , new SqlParameter("ref_no", model.RefNo)
                        , new SqlParameter("doc_ref_no", model.DocRefNo)
                        , new SqlParameter("money_req_name", model.MoneyReqName)
                        , new SqlParameter("bgm_proj_oper_seq", model.BgProjOperId)
                        , new SqlParameter("emp_seq", model.EmpId)
                        , new SqlParameter("job_site_seq", model.JobSiteId)
                        , new SqlParameter("contact_seq", model.ContactId)
                        , new SqlParameter("icm_dn_seq", model.DnId)
                        , new SqlParameter("record_status", model.RecordStatus)
                        , new SqlParameter("created_by", model.CreatedBy)
                        , new SqlParameter("created_when", model.CreatedWhen)
                        , new SqlParameter("allow", model.Allow)
                        , new SqlParameter("bgm_year", model.BgYear)
                        , new SqlParameter("work_area_seq", work_area_seq)
                        );

                    if (ret > 0)
                    {
                        int money_req_id = GetMoneyReqId(model.DnId);
                        sb = new StringBuilder();
                        sb.Append("INSERT INTO fim_money_request_dtls (");
                        sb.Append(" money_req_seq");
                        sb.Append(" ,bgm_proj_oper_exp_seq");
                        sb.Append(" ,job_site_seq");
                        sb.Append(" ,amount");
                        sb.Append(" ,amount_vat");
                        sb.Append(" ,record_status");
                        sb.Append(" ,created_by");
                        sb.Append(" ,created_when");
                        sb.Append(" )");
                        sb.Append(" VALUES(");
                        sb.Append(" @money_req_seq");
                        sb.Append(" ,@bgm_proj_oper_exp_seq");
                        sb.Append(" ,@job_site_seq");
                        sb.Append(" ,@amount");
                        sb.Append(" ,@amount_vat");
                        sb.Append(" ,@record_status");
                        sb.Append(" ,@created_by");
                        sb.Append(" ,@created_when");
                        sb.Append(");");

                        ret = context.Database.ExecuteSqlCommand(sb.ToString()
                       , new SqlParameter("money_req_seq", money_req_id)
                       , new SqlParameter("bgm_proj_oper_exp_seq", model.BgProjOperExpId)
                       , new SqlParameter("job_site_seq", model.JobSiteId)
                       , new SqlParameter("amount", model.Amount)
                       , new SqlParameter("amount_vat", model.AmountVat)
                       , new SqlParameter("record_status", model.RecordStatus)
                       , new SqlParameter("created_by", model.CreatedBy)
                       , new SqlParameter("created_when", model.CreatedWhen)
                       );

                        //if (ret > 0) { 
                        //    UpdateUsed(model.DnId, model.CreatedBy);
                        //}
                    }
                }

            }
            catch(Exception ex)
            {
                throw ex;
            }
           
            return ret;
        }

        public int GetMoneyReqId(int? DnId)
        {
            try
            {
                int ret = 0;
                string sql = "SELECT Max(money_req_seq) FROM fim_money_requests"
                    + " WHERE icm_dn_seq = " + DnId;

                using (var context = new GERPContext(false))
                {
                    var lst = context.Database.SqlQuery<int>(sql);
                    if (lst != null)
                    {
                        ret = lst.FirstOrDefault();
                    }

                    //int cnt = query.Where(a => a.CreatedWhen.Value.Year == year && a.CreatedWhen.Value.Month == month).OrderByDescending(a => a.PrId).Count();

                    return ret;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int GetWorkAreaId(int? jobsiteid)
        {
            try
            {              
                int ret = 0;
                string sql = "SELECT work_area_seq FROM hrm_mp_job_sites"
                    + " WHERE job_site_seq = " + jobsiteid;

                using (var context = new GERPContext(false))
                {
                    var lst = context.Database.SqlQuery<int>(sql);
                    if (lst != null)
                    {
                        ret = lst.FirstOrDefault();                       
                    }
                    
                    //int cnt = query.Where(a => a.CreatedWhen.Value.Year == year && a.CreatedWhen.Value.Month == month).OrderByDescending(a => a.PrId).Count();
                    
                    return ret;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int Insert(DeliveryNote model, int createdByUserID)
        {

            using (var context = new GERPContext())                                  
            {
                model.RecordStatus = RecordStatus.WaitStatus;
                model.CreatedBy = createdByUserID;
                model.CreatedWhen = DateTime.Now;
                string code = "";

                // bug: unknown cause, some time Dn.Code save 'null', this allow program retry 3 times then throw an error if it can not generate corrected code
                // bug: if it's still generate null on field Code again and can be save null value on Database, try debug another way
                if(string.IsNullOrWhiteSpace((code = CodeGenerate.GetNextCode(CodeGenerate.TransectionType.DeliveryNote, ""))))
                {
                    if(string.IsNullOrWhiteSpace(code = CodeGenerate.GetNextCode(CodeGenerate.TransectionType.DeliveryNote, "")))
                    {
                        if(string.IsNullOrWhiteSpace(code = CodeGenerate.GetNextCode(CodeGenerate.TransectionType.DeliveryNote, "")))
                        {
                            throw new Exception("ไม่สามารถสร้างรหัสอ้างอิง ได้ กรุณาลองใหม่กดบันทึกใหม่อีกครั้ง");
                        }
                    }
                }
                model.Code = code;
                context.DeliveryNotes.Add(model);
                context.SaveChanges();

                if (model.DnId != 0)
                {
                   // DeliveryNoteItem DnItem = new DeliveryNoteItem();
                    //DeliveryNotePenalty DnPen = new DeliveryNotePenalty();
                    PurchaseOrderInstallmentItem POInstall = new PurchaseOrderInstallmentItem();
                    //PurchaseOrderPenalty POPen =new PurchaseOrderPenalty();
                    //PurchaseRequestItem PrItem = new PurchaseRequestItem();

                    var data = (from a in context.PurchaseOrderInstallmentItems
                                where a.PoInstallId == model.PoInstallId
                                select a).Include("BidderQuotation.PurchaseRequestItem").ToList();
                                
                    foreach (var a in data)
                    {
                        if (a.Qty > 0)
                        {
                            DeliveryNoteItem DnItem = new DeliveryNoteItem();
                            DnItem.PoInstallItemId = a.PoInstallItemId;
                            DnItem.DnId = model.DnId;
                            DnItem.ItemQty = a.Qty;
                            DnItem.ResItemId = a.BidderQuotation.PurchaseRequestItem.ResItemId;
                            DnItem.ItemPriceIncludeVat = a.ItemPriceIncludeVat;
                            DnItem.ItemPriceExcludeVat = a.ItemPriceExcludeVat;
                            DnItem.NetAmount = a.NetAmount;
                            DnItem.VatAmount = a.VatAmount;

                            context.DeliveryNoteItems.Add(DnItem);
                            context.SaveChanges();
                        }
                    }

                    //var dataPopen = (from b in context.PurchaseorderPenalties
                    //                 where b.PoId == model.PoId
                    //                 select b).ToList();
                    //foreach (var QryPopen in dataPopen)
                    //{
                    //    //DnPen.DnId = model.DnId;
                    //    //DnPen.PoPenId = QryPopen.PoPenId;
                    //    model.CreatedBy = createdByUserID;
                    //    DnPen.DnId = model.DnId;
                    //    DnPen.PoPenId = 1;
                    //    context.DeliveryNotePenalties.Add(DnPen);
                    //    context.SaveChanges();
                    //}
                }

                return model.DnId;
            }
        }

        public void UpdateUsed(DeliveryNote model, int createByUserId)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    context.Entry(model).State = EntityState.Modified;
                    model.RecordStatus = "U";
                    model.UpdatedBy = createByUserId;
                    model.UpdatedWhen = DateTime.Now;
                    if (string.IsNullOrWhiteSpace(model.Code))
                    {
                        throw new Exception("Dn code null 18");
                    }
                    context.SaveChanges();
                }
            }
            catch (Exception ex)
            {
                throw (ex);

            }

        }

        public void DeleteDeliveryNote(int id, int deleteByUserId)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    var modelToDelete = context.DeliveryNotes.Find(id);
                    if (modelToDelete == null) return;
                    modelToDelete.RecordStatus = Models.SYS.RecordStatus.DeletedStatus;
                    context.Entry(modelToDelete).State = EntityState.Modified;
                    context.SaveChanges();
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }

        }

        #region UpdateDeliveryNote
        public void Update(DeliveryNote model, int createByUserId)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    context.Entry(model).State = EntityState.Modified;
                    model.UpdatedBy = createByUserId;
                    model.UpdatedWhen = DateTime.Now;
                    if (string.IsNullOrWhiteSpace(model.Code))
                    {
                        throw new Exception("Dn code null 199");
                    }
                    context.SaveChanges();
                }
            }
                catch (Exception ex)
            {
                    throw(ex);
                   
             }

        }
        #endregion
        //public List<SelectListItem> GetPoName(PurchaseOrder criteriaModel = null)
        //{
        //    var list = new List<SelectListItem>();
        //    list.Add(new SelectListItem { Selected = true, Text = "....เลือกPOที่ต้องการตรวจรับ....", Value = "" });

        //    try
        //    {
        //        using (var context = new GERPContext(false))
        //        {
        //            IQueryable<PurchaseOrder> query = context.PurchaseOrders;

        //            if (criteriaModel != null) 
        //            {
        //                object[] param;
        //                query = query.Where(KPIS.GERP.Models.Class.Utility.GenerateWhereString(criteriaModel, out param), param);
        //            }

        //            var x = query.ToList();
        //            foreach (var model in x)
        //            {
        //                var item = new SelectListItem { Text = model.PoId.ToString(), Value = model.PoId.ToString() };
        //                list.Add(item);
        //            }
        //        }

        //    }
        //    catch (Exception ex)
        //    {
        //        throw ex;
        //    }
        //    return list;

        //}


        //public string GetPoInstallment (int PoInstallId)
        //{
        //    string strPoIstallQty;
        //    var context = new GERPContext(false);

        //    try
        //    {
        //        var PoInstall = (from i in context.PurchaseOrderInstallmentItems
        //                         where i.IcmPoInstallId == PoInstallId
        //                         select i.ItemQty).ToList();
        //        strPoIstallQty = PoInstall.ToString();

        //    }
        //    catch (Exception ex)
        //    {
        //        throw ex;
        //    }
        //    return strPoIstallQty;
        // }
    }
}
